const Model = require('./index')

class StaffModel extends Model {
  /**
   * 分页查询员工列表
   * @param {*} pageNo
   * @param {*} pageSize
   * @param {*} andConditions
   * @param {*} orConditions
   * @returns
   */
  async findPage ({ pageNo, pageSize, andConditions, orConditions }) {
    const andSql = this.generateWhereClause(andConditions)
    const completeSql = this.generateWhereClauseOR(orConditions, andSql)
    const sql = `select staff.*, group_concat(role.name) as roleNames, group_concat(role.id) as roleIds from staff 
      left join staff_role sr on sr.staffId = staff.id
      left join role on sr.roleId = role.id ${completeSql}
      group by staff.id 
      order by staff.createTime desc
      limit ${pageSize} offset ${(pageNo - 1) * pageSize}`
    try {
      const list = await this.db.query(sql)
      const total = await this.total(completeSql)
      return {
        list,
        total
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  async total (conditionSql) {
    const sql = `select count(*) as total from staff ${conditionSql}`
    try {
      const data = await this.db.query(sql)
      return data[0].total
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 员工创建
   * @param {*} attrs
   */
  async create (attrs) {
    const { username, password, nickname, gender, status, avatar, email, tel, age } = attrs
    const createTime = this.dateFormat(new Date())
    const sql = `insert into staff (username, password, nickname, gender, status, avatar, email, tel, age, createTime)
      values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
    try {
      await this.db.query(sql, [username, password, nickname, gender, status, avatar, email, tel, age, createTime])
      const userInfo = await this.existUsername(username)
      return userInfo.id
    } catch (error) {
      throw new Error(error)
    }
  }

  async existUsername (username) {
    const sql = 'select username, id from staff where username = ?'
    try {
      const data = await this.db.query(sql, [username])
      return data[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 根据usernam查询完整的用户信息
   * @param {*} username
   */
  async findOneByUsername (username) {
    const sql = 'select * from staff where username = ?'
    try {
      const data = await this.db.query(sql, [username])
      return data[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 更新员工信息
   * @param {number} id
   * @param {*} attrs
   */
  async update (id, attrs) {
    const { nickname, gender, avatar, email, tel, age } = attrs
    const { columnSet, values } = this.multipleColumnSet({
      nickname,
      gender,
      avatar,
      email,
      tel,
      age,
      updateTime: this.dateFormat(new Date())
    })
    const sql = `update staff set ${columnSet} where id = ?`
    try {
      await this.db.query(sql, [...values, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   *
   * @param {number} id
   * @param {string} status
   */
  async updateStatus (id, status) {
    const sql = 'update staff set status = ? where id = ?'
    try {
      await this.db.query(sql, [status, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  async delete (id) {
    const sql = 'delete from staff where id = ?'
    try {
      await this.db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 批量删除员工
   * @param {*} ids
   */
  async batchDelete (ids) {
    const sql = `delete staff, staff_role from staff 
      left join staff_role on staff_role.staffId = staff.id where staff.id in (?)`
    try {
      await this.db.query(sql, ids.join(','))
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 密码更新
   * @param {*} username
   * @param {*} password
   */
  async updatePassword (username, password) {
    const sql = 'update staff set password = ? where username = ?'
    try {
      await this.db.query(sql, [password, username])
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new StaffModel('staff')
